﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using DreamBuilder.DictionaryAccess;

namespace DreamBuilder.UI.UserControls.DataAccess.Services
{
    public abstract class UCEditDAL : UserControlsDataAccessBase
    {
        public bool IsReferenceTableHasValue(string tableCode, string initValue)
        {
            TableDicInfo pTableDicInfo = DictionaryAccessFacade.GetTableDicInfo(tableCode);
            string primaryField = pTableDicInfo.PrimaryField;
            string sql = "select count(*) from " + tableCode + " where " + primaryField + " = '" + initValue + "'";
            object count = DBHelper.ExecuteScalar(sql);
            if (count == null || int.Parse(count.ToString()) == 0)
            {
                return false;

            }
            {
                return true;
            }
        }

        public IDataReader GetMultiReferenceInfo(FieldDicInfo pFieldDicInfo, string sRefCodeValues)
        {
            string strFromSql = "";
            string strSelectSql = "";
            string joinMainTable = "";
            string joinMainField = "";
            string referenceObjShowColumn = "";
            string referenceMainTable = "";
            TableDicInfo pTableDicInfo = DictionaryAccessFacade.GetTableDicInfo(pFieldDicInfo.ReferenceObj);
            string sPrimaryField = pTableDicInfo.PrimaryField;
            if (pTableDicInfo != null)
            {
                strFromSql = pTableDicInfo.TableCode;
            }
            referenceObjShowColumn = pTableDicInfo.DisplayField.Trim();
            referenceMainTable = pTableDicInfo.TableCode.Trim();
            joinMainTable = pTableDicInfo.TableCode;
            joinMainField = pTableDicInfo.DisplayField;

            pFieldDicInfo = DictionaryAccessFacade.GetFieldDicInfo(pTableDicInfo.TableCode.ToString().Trim(), pTableDicInfo.DisplayField.ToString().Trim());

            if (pFieldDicInfo.ReferenceObj != "")
            {
                pTableDicInfo = DictionaryAccessFacade.GetTableDicInfo(pFieldDicInfo.ReferenceObj);
            }
            else
            {
                pTableDicInfo = null;
            }

            int i = 0;
            while (pTableDicInfo != null)
            {
                if (pTableDicInfo.DisplayField != null)
                {
                    referenceObjShowColumn = pTableDicInfo.DisplayField.Trim();
                    referenceMainTable = pTableDicInfo.TableCode.Trim();
                    i = 0;

                    //如果from语句中已经存在此参照表，则将i递增
                    if (strFromSql.ToLower().IndexOf(" join " + pTableDicInfo.TableCode.ToLower() + " on") > 0)
                    {
                        i++;
                    }
                    //如果i递增后，仍然在from语句中存在此参照表，则不断循环递增
                    while (strFromSql.ToLower().IndexOf(" join " + pTableDicInfo.TableCode + "_" + i.ToString() + " on") > 0)
                    {
                        i++;
                    }

                    if (i == 0)
                    {
                        strFromSql = strFromSql + " left join " + pTableDicInfo.TableCode + " on " + joinMainTable.ToString().Trim() + "." + joinMainField.ToString().Trim() +
                            " = " + pTableDicInfo.TableCode + "." + pTableDicInfo.PrimaryField;
                        joinMainTable = pTableDicInfo.TableCode;
                        joinMainField = pTableDicInfo.DisplayField;
                    }
                    else
                    {
                        strFromSql = strFromSql + " left join " + pTableDicInfo.TableCode + " as " + pTableDicInfo.TableCode + "_" + i.ToString() + " on " + joinMainTable.ToString().Trim() + "." + joinMainField.ToString().Trim() +
                            " = " + pTableDicInfo.TableCode + "_" + i.ToString() + "." + pTableDicInfo.PrimaryField;
                        joinMainTable = pTableDicInfo.TableCode + "_" + i.ToString();
                        joinMainField = pTableDicInfo.DisplayField;
                    }

                    pFieldDicInfo = DictionaryAccessFacade.GetFieldDicInfo(pTableDicInfo.TableCode.ToString().Trim(), pTableDicInfo.DisplayField.ToString().Trim());

                    if (pFieldDicInfo.ReferenceObj != null && pFieldDicInfo.ReferenceObj != "")
                    {
                        pTableDicInfo = DictionaryAccessFacade.GetTableDicInfo(pFieldDicInfo.ReferenceObj);
                    }
                    else
                    {
                        pTableDicInfo = null;
                    }
                }
            }

            if (i == 0)
            {
                strSelectSql = referenceMainTable + "." + referenceObjShowColumn;
            }
            else
            {
                strSelectSql = referenceMainTable + "_" + i.ToString() + "." + referenceObjShowColumn;
            }

            sRefCodeValues = "'" + sRefCodeValues.Replace(",", "','") + "'";
            string sSql = "select " + strSelectSql + " from " + strFromSql + " where " + sPrimaryField + " in (" + sRefCodeValues + ")";
            return DBHelper.ExecuteReader(sSql);
        }

        public IDataReader GetEditPKValueInfo(string editID, string brotherKeyValue, string whereSql)
        {
            VoucherTotalInfo pTotalInfo = DictionaryAccessFacade.GetVoucherTotalInfo(editID);
            TableDicInfo pTableDicInfo = DictionaryAccessFacade.GetTableDicInfo(pTotalInfo.MainTable);
            string sSql = "select " + pTableDicInfo.PrimaryField + " from " + pTableDicInfo.TableCode + " where " + pTableDicInfo.ParentConnectField + " = '" + brotherKeyValue + "'";
            if (!string.IsNullOrEmpty(whereSql))
                sSql += " and " + whereSql;
            return DBHelper.ExecuteReader(sSql);
        }

        public IDataReader GetExceptColumnInfo(string editID, DataTable dtExceptInfo, string mainTablePKName, string mainTablePKValue)
        {
            VoucherTotalInfo pTotalInfo = DictionaryAccessFacade.GetVoucherTotalInfo(editID);
            string selectSql = "select ";
            foreach (DataColumn dcInfo in dtExceptInfo.Columns)
            {
                selectSql += "" + dcInfo.ColumnName + " as \"" + dcInfo.ColumnName + "\",";
            }
            selectSql = selectSql.TrimEnd(',');

            string fromSql = " from ";
            if (!string.IsNullOrEmpty(pTotalInfo.TableConnectSql))
            {
                fromSql += pTotalInfo.TableConnectSql;
            }
            else
            {
                fromSql += pTotalInfo.MainTable;
            }
            //exceptSql += " where " + mainTablePK + " = '" + Common.CStringFormat.SqlStrFormat(PKValue) + "'";
            string sSql = selectSql + fromSql + " where " + mainTablePKName + " = '" + mainTablePKValue + "'";
            return DBHelper.ExecuteReader(sSql);
        }
        abstract public System.Data.DataTable GetEditControlsData(string EditID, System.Collections.Generic.List<VoucherEditItemInfo> list, string PKValue);
    }
}
